package com.du.lin.utils;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.du.lin.bean.ShowLog;
import com.du.lin.service.LoginLogService;
import com.du.lin.service.OperationLogService;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.springframework.util.StringUtils;

/**
 * Excel工具类
 */
@Component
public class ExcelUtil {
	@Autowired
	private LoginLogService loginLogService;
	@Autowired
	private OperationLogService operationLogService;

	/**
	 * 生成excel文件 在D:\
	 * 1生成登陆日志
	 * 2生成操作日志
	 * @param logType
	 * @return
	 */
	public File getExcel(int logType){
		List<ShowLog> list = null;
		if (logType == 1) {
			list = loginLogService.getAllShowLoginLog();
		}else if(logType == 2){
			list = operationLogService.getAllShowLog();
		}

		File file  = new File("d:\\"+ System.currentTimeMillis() +".xls");
		WritableWorkbook workbook = null;
		try {
			workbook = Workbook.createWorkbook(file);
			WritableSheet sheet = workbook.createSheet("sheet1", 0);
			WritableCellFormat wcf = new WritableCellFormat();
			wcf.setBackground(Colour.YELLOW);
			sheet.addCell(new Label(0, 0, "用户名" , wcf));
			sheet.addCell(new Label(1, 0, "操作", wcf));
			sheet.addCell(new Label(2, 0, "时间", wcf));
			sheet.setColumnView(0, 40);
			sheet.setColumnView(1, 20);
			sheet.setColumnView(2, 30);
			for (int row = 0; row < list.size(); row++) {
				sheet.addCell(new Label(0, row+1, list.get(row).getUsername()));
				sheet.addCell(new Label(1, row+1, list.get(row).getLogname()));
				sheet.addCell(new Label(2, row+1, list.get(row).getCreatetime()));
			}

		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}finally {
			if (workbook != null) {
				try {
					workbook.write();
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (WriteException e) {
					e.printStackTrace();
				}
			}
		}

		return file;

	}


	/**(
	 * 电销管理excel
	 * @param
	 * @return
	 */
	public File getSalesExcel(List<Object> list){
		if(StringUtils.isEmpty(list)){
			return null;
		}
		//取时间
		String date = ((Map)list.get(0)).get("date").toString();
		//算出当月有多少天
		int sumDay = 0 ;
		//获取月份
		int month = 0;
		try {
			 sumDay = DateUtil.getDaysByYearMonth(date);
			 month = DateUtil.getDaysByMonth(date);
		}catch (Exception e){
			e.printStackTrace();
		}


		//列

		File file  = new File("d:\\"+ System.currentTimeMillis() +".xls");
		WritableWorkbook workbook = null;
		try {
			workbook = Workbook.createWorkbook(file);
			WritableSheet sheet = workbook.createSheet("sheet1", 0);
			WritableCellFormat wcf = new WritableCellFormat();
			//设置对齐方式
			wcf.setAlignment(jxl.format.Alignment.CENTRE);
			// 列  行  标题，格式
			sheet.addCell(new Label(0, 0, month+"月份电销" , wcf));
			// 列  行 ，列，行
			sheet.mergeCells(0,0,2+sumDay,0);
			// 列  行  标题，格式
			sheet.addCell(new Label(0, 1, "姓名" , wcf));
			// 列  行 ，列，行
			sheet.mergeCells(0,1,0,2);
			// 列  行  标题，格式
			sheet.addCell(new Label(1, 1, "工作统计" , wcf));
			// 列  行 ，列，行
			sheet.mergeCells(1,1,1,2);
			// 列  行  标题，格式
			sheet.addCell(new Label(2, 1, "总计" , wcf));
			// 列  行 ，列，行
			sheet.mergeCells(2,1,2,2);

			// 列  行  标题，格式
			sheet.addCell(new Label(3, 1, "时间" , wcf));
			// 列  行 ，列，行
			sheet.mergeCells(3,1,2+sumDay,1);
			for(int i=0;i<sumDay;i++){
				// 列  行  标题，格式
				sheet.addCell(new Label(3+i, 2, i+1+"" , wcf));
				// 列  行 ，列，行
				sheet.mergeCells(3+i,2,3+i,1);
			}
			for (int row = 0; row < list.size(); row++) {
				Map<String,Object> map = (Map<String,Object>)list.get(row);
				if(row==0||row/8==1){
					sheet.addCell(new Label(0, row+3, String.valueOf(map.get("userName")),wcf));
					// 列  行 ，列，行
					sheet.mergeCells(0,row+3,0,row+10);
				}
				sheet.addCell(new Label(1, row+3, String.valueOf(map.get("type"))));
				sheet.addCell(new Label(2, row+3, String.valueOf(map.get("sum"))));
				for(int j=0;j<sumDay;j++){
					sheet.addCell(new Label(j+3, row+3, String.valueOf(map.get("day"+(j+1)))));
				}
				//sheet.addCell(new Label(1, row+1, list.get(row).getLogname()));
				//sheet.addCell(new Label(2, row+1, list.get(row).getCreatetime()));
			}

		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}finally {
			if (workbook != null) {
				try {
					workbook.write();
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (WriteException e) {
					e.printStackTrace();
				}
			}
		}

		return file;

	}
	
}
